package tingshu.repository;


import lombok.SneakyThrows;
import tingshu.data_object.TrackDO;
import tingshu.util.DBUtil;
import tingshu.util.Log;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class TrackRepo {
    @SneakyThrows
    public void insert(int uid, String title, String type, InputStream content) {
        String sql = "insert into tracks (uid, title, type, content) values (?, ?, ?, ?);";

        try (Connection c = DBUtil.connection()) {
            try (PreparedStatement ps = c.prepareStatement(sql)) {
                ps.setInt(1,uid);
                ps.setString(2,title);
                ps.setString(3,type);
                ps.setBinaryStream(4,content);

                Log.println("执行 SQL" + ps.toString());
                ps.executeUpdate();
            }
        }
    }

    @SneakyThrows
    public List<TrackDO> selectListByUidLimitOffset(int uid, int limit, int offset) {
        List<TrackDO> list = new ArrayList<>();
        String sql = "select tid, uid, title, type from tracks where uid = ? order by tid limit ? offset ?";
        try (Connection c = DBUtil.connection()) {
            try (PreparedStatement ps = c.prepareStatement(sql)) {
                ps.setInt(1, uid);
                ps.setInt(2, limit);
                ps.setInt(3, offset);
                Log.println("执行 SQL" + ps.toString());
                ps.executeQuery();
                try (ResultSet rs = ps.getResultSet()) {
                    while (rs.next()) {
                        TrackDO trackDO = new TrackDO(
                                rs.getInt("tid"),
                                rs.getInt("uid"),
                                rs.getString("title"),
                                rs.getString("type")
                        );
                        list.add(trackDO);
                    }
                }
            }
        }
        return list;
    }

    @SneakyThrows
    public int selectCountByUid(int uid) {
        String sql = "select count(*) from tracks where uid = ?";
        try (Connection c = DBUtil.connection()) {
            try (PreparedStatement ps = c.prepareStatement(sql)) {
                ps.setInt(1, uid);
                Log.println("执行 SQL" + ps);
                ps.executeQuery();

                try (ResultSet rs = ps.getResultSet()) {
                    rs.next();
                    return rs.getInt(1);
                }
            }
        }
    }

    @SneakyThrows
    public List<TrackDO> selectListByTidList(List<Integer> tidList) {
        StringBuilder sb = new StringBuilder();
        for (Integer tid : tidList) {
            sb.append(tid).append(", ");
        }
        sb.delete(sb.length() - 2, sb.length());
        //String s = String.join(", ", tidList.stream().map(String::valueOf).collect(Collectors.toList()));

        String sql = String.format("select tid, uid, title, type from tracks where tid in (%s) order by tid",sb);

        List<TrackDO> trackDOList = new ArrayList<>();
        try (Connection c = DBUtil.connection()) {
            try (PreparedStatement ps = c.prepareStatement(sql)) {
                Log.println("执行 SQL" + ps);
                try (ResultSet rs = ps.executeQuery()) {
                    while (rs.next()) {
                        trackDOList.add(new TrackDO(
                                rs.getInt("tid"),
                                rs.getInt("uid"),
                                rs.getString("title"),
                                rs.getString("type")
                        ));
                    }
                }
            }
        }
        return trackDOList;
    }
}
